package com.otis.DataFetch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class controllerdata {

    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        final String Num = "0000010";
        Labcon lc = new Labcon();
        ArrayList arr = new ArrayList();

        String delQry = "DELETE from webstarsadgrk.dbo.tblEvent";
        con = lc.getLocalConnection();
        try {
            ps = con.prepareStatement(delQry);
            ps.executeUpdate();
            con.close();
            ps.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        String delQry1 = "DELETE from webstarsadgrk.dbo.tblAtnd";
        con = lc.getLocalConnection();
        try {
            ps = con.prepareStatement(delQry1);
            ps.executeUpdate();
            con.close();
            ps.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        String insrtQry = "INSERT INTO webstarsadgrk.dbo.tblEvent(CardNum,Event_time_UTC) SELECT CARDNUM,EVENT_TIME_UTC FROM [access control].dbo.EVENTS WHERE CARDNUM <>'0' and (EVENTCODE = '' or EVENTCODE is null) and event_time_utc >'2014-12-01'";
        con = lc.getLocalConnection();
        try {
            ps = con.prepareStatement(insrtQry);
            ps.executeUpdate();
            con.close();
            ps.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        // String selectQry = "Select cardnum,event_time_utc,substring(serialnum,5,5) from events where CARDNUM <>'0' and (EVENTCODE = '' or EVENTCODE is null) and event_time_utc >'2014-12-01'";
        String selectQry = "select cardnum, event_time_utc,substring(CONVERT(varchar(10), serialnum),5,5) as serial from [Access Control].dbo.EVENTS where cardnum<>0 and (EVENTCODE='' or EVENTCODE is null) and event_time_utc >'2014-12-01'";
        con = lc.getLocalConnectionAccessControl();
        try {
            ps = con.prepareStatement(selectQry);
            rs = ps.executeQuery();

            while (rs.next()) {
                String card = String.format("%010d", rs.getInt(1));

                String Date = new SimpleDateFormat("yyyyMMddhhmm").format(rs.getTimestamp(2));

                String Ser = rs.getString(3);

                arr.add(Num + card + Date + " " + "I" + Ser + "01");

                for (Object element : arr) {
                    System.out.println(element);
                    String InsrtQry2 = "insert into webstarsadgrk.dbo.tblAtnd values ('"+element+"')";
                    con = lc.getLocalConnection();
                    try {
                        ps = con.prepareStatement(InsrtQry2);
                        ps.executeUpdate();
                        con.close();
                        ps.close();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            }
            con.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        String updateQry = "UPDATE t1 set t1.EVENTCODE = '1' from [access control].dbo.EVENTS t1,webstarsadgrk.dbo.tblEvent t2 where t1.CARDNUM = t2.CardNum and t1.EVENT_TIME_UTC=t2.Event_time_UTC";
         con = lc.getLocalConnectionAccessControl();
         try {
         ps = con.prepareStatement(updateQry);
         ps.executeUpdate();
         con.close();
         ps.close();
         } catch (SQLException ex) {
         ex.printStackTrace();
         }
         

    }
}